package de.stalinski.db;

import de.stalinski.util.Logging;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Enumeration;
import java.util.Map;
import javax.servlet.http.HttpServlet;

public class PricesTableConnector extends HttpServlet
{

    private static String _sUsr = "root";

    private static String _sPwd = "admin";

    private static String _sDB = "frbrUsers";

    private static String _sTable = "prices_drinks";

    private static Connection _connection;

    private static String _sHost = "localhost";

    private static String _sConn = "jdbc:mysql://" + _sHost + ":3306/" + _sDB;

    private ArrayList<String> _drinks = new ArrayList();

    public PricesTableConnector() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        try {
            if ( _connection == null ) {
                _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            }
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
    }

    public Double getPriceFor(String sort) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            if ( _connection.isClosed() ) {
                try {
                    _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
                } catch ( SQLException ex ) {
                    Logging.log(Logging.ERROR, ex, this.getClass().getName());
                }
            }
            try {
                Statement st = null;
                ResultSet rs = null;
                st = _connection.createStatement();
                rs = st.executeQuery("select SORT,VALUE from " + _sTable);
                while ( rs.next() ) {
                    String value = rs.getString("SORT");
                    if ( value.equals(sort) ) {
                        return Double.parseDouble(rs.getString("VALUE"));
                    }
                }
                Collections.sort(_drinks);
            } catch ( SQLException ex ) {
                Logging.log(Logging.ERROR, ex, this.getClass().getName());
            }
            _connection.close();

        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return 0.0;
    }

    public void updateStock(Map<String, String[]> pMap,
            Enumeration<String> pEnum) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            st = _connection.createStatement();
            String sort = "";
            int amount = 0;
            int bottles = 0;
            String tAmount = null;
            boolean failure;
            while ( pEnum.hasMoreElements() ) {
                failure = false;
                sort = pEnum.nextElement();
                bottles = getBottlesPerBox(sort);
                tAmount = new String(pMap.get(sort)[0]);
                try {
                    amount = (Integer.parseInt(tAmount)) * bottles;
                } catch ( NumberFormatException ex ) {
                    Logging.log(Logging.ERROR, ex, this.getClass().getName());
                    failure = true;
                }
                if ( !failure ) {
                    st.execute("UPDATE " + _sTable + " SET STOCK=STOCK+" + amount + " WHERE SORT ='" + sort + "'");
                }
            }
            _connection.close();
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
    }

    public String buildUpdateTable() {
        String out = "";
        try {
            out += "<form name=\"updateStock\" method=\"GET\" action=\"doUpdate.jsp\">";
            out += "<table class=\"Prices\" cellspacing=0>";
            out += "<thead>";
            out += "<th>Sorte</th><th>Anzahl Kisten</th>";
            out += "</thead>";
            out += "<tbody>";
            out += "<tr>";
            out += "<td colspan=2>";
            out += "<textarea name=\"info\" rows=\"1\" cols=\"100\" readonly=\"readonly\">Information...</textarea>";
            out += "</td>";
            out += "</tr>";
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable + " ORDER BY SORT ASC");
            double boxes = 0.0;
            while ( rs.next() ) {
                boxes = Math.round((rs.getDouble("STOCK") / getBottlesPerBox(rs.
                        getString("SORT"))) * 10.0) / 10.0;
                out += "<tr>";
                out += "<td>" + rs.getString("SORT") + "</td>";
                out += "<td><input type=\"text\" name=\"" + rs.getString("SORT") + "\" onmouseout=\"clearMe()\" onmouseover=\"printMe('" + rs.
                        getString("STOCK") + " ',' " + boxes + "')\" /></td>";
                out += "</tr>";
            }
            out += "<tr>";
            out += "<td><input type=\"reset\" value=\"Reset\"/></td>";
            out += "<td><input type=\"submit\" value=\"Buchen\"/></td>";
            out += "</tr>";
            out += "</tbody>";
            out += "</table>";
            out += "</form>";
            _connection.close();

        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return out;
    }

    public String getStock(int days) {
        String out = "";
        try {
            out += "<table class=\"Prices\" cellspacing=0>";
            out += "<thead>";
            out += "<th>Sorte</th><th>Anzahl Flaschen</th><th>Anzahl Kisten</th><th>Einkaufswert</th><th>Verkaufswert</th><th>Gewinn</th><th>Aufgebraucht am (ca.)</th><th>Empfohlene Bestellmenge für " + days + " Tag(e)</th>";
            out += "</thead>";
            out += "<tbody>";
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable + " ORDER BY SORT ASC");
            double boxes = 0.0;
            double bStock = 0.0;
            double bBoxesResult = 0.0;
            double valueOut = 0.0;
            double costIn = 0.0;
            double profit = 0.0;
            BigDecimal stockValueIn = new BigDecimal(0.0);
            BigDecimal stockValueOut = new BigDecimal(0.0);
            BigDecimal stockProfit = new BigDecimal(0.0);
            double temp = 0.0;
            while ( rs.next() ) {
                costIn = (rs.getDouble("PRICE_ZISO"));
                temp = rs.getDouble("VALUE") * rs.getDouble("STOCK");
                valueOut = temp;
                bStock = rs.getDouble("STOCK");
                boxes = (this.getBottlesPerBox(rs.getString("SORT")));
                bBoxesResult = bStock / boxes;
                costIn = (costIn / boxes) * bStock;
                profit = (valueOut - costIn);
                stockValueIn = stockValueIn.add(new BigDecimal(costIn)).
                        setScale(2, BigDecimal.ROUND_CEILING);
                stockValueOut = stockValueOut.add(new BigDecimal(valueOut)).
                        setScale(2, BigDecimal.ROUND_CEILING);
                stockProfit = stockProfit.add(new BigDecimal(profit)).
                        setScale(2, BigDecimal.ROUND_CEILING);
                out += "<tr bgcolor=\"#FFFFFF\">";
                out += "<td>" + rs.getString("SORT") + "</td>";
                out += "<td>" + rs.getString("STOCK") + "</td>";
                out += "<td>" + new BigDecimal(bBoxesResult).setScale(2, BigDecimal.ROUND_CEILING) + "</td>";
                out += "<td>" + new BigDecimal(costIn).setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&euro;</td>";
                out += "<td>" + new BigDecimal(valueOut).setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&euro;</td>";
                out += "<td>" + new BigDecimal(profit).setScale(2, BigDecimal.ROUND_CEILING) + "&nbsp;&euro;</td>";
                try {
                    out += "<td>" + new BookingsConnector().
                            getEstimatedStockEmpty(rs.getString("SORT"), rs.
                            getInt("STOCK"), days)[0] + "</td>";
                    out += "<td>" + new BookingsConnector().
                            getEstimatedStockEmpty(rs.getString("SORT"), rs.
                            getInt("STOCK"), days)[1] + "</td>";
                } catch ( NullPointerException ex ) {
                    Logging.log(Logging.ERROR, ex, this.getClass().getName());
                    out += "<td>" + "-" + "</td>";
                    out += "<td>" + "-" + "</td>";
                }
                out += "</tr>";
            }
            out += "<tr bgcolor=\"#585858\"><td colspan=6>&nbsp;</td></tr>";
            out += "<tr bgcolor=\"#FFFFFF\">";
            out += "<td colspan=3>Summen:</td>";
            out += "<td>" + stockValueIn + "&nbsp;&euro;</td>";
            out += "<td>" + stockValueOut + "&nbsp;&euro;</td>";
            out += "<td>" + stockProfit + "&nbsp;&euro;</td>";
            out += "</tr>";
            out += "</tbody>";
            out += "</table>";
            _connection.close();

        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return out;
    }

    public int getBottlesPerBox(String sort) {
        int bottles = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable);

            while ( rs.next() ) {
                if ( rs.getString("SORT").equals(sort) ) {
                    bottles = rs.getInt("BOTTLES");
                    _connection.close();
                    return bottles;
                }
            }
            _connection.close();

        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return -1;
    }

    public double getPricePerBox(String sort) {
        double price = 0.0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable);

            while ( rs.next() ) {
                if ( rs.getString("SORT").equals(sort) ) {
                    price = rs.getDouble("PRICE_ZISO");
                    _connection.close();
                    return price;
                }
            }
            _connection.close();
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return -1.0;
    }

    public int getStockValueForSort(String sort) {
        int stock = 0;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            _connection = DriverManager.getConnection(_sConn, _sUsr, _sPwd);
            Statement st = null;
            ResultSet rs = null;
            st = _connection.createStatement();
            rs = st.executeQuery("select * from " + _sTable);

            while ( rs.next() ) {
                if ( rs.getString("SORT").equals(sort) ) {
                    stock = rs.getInt("STOCK");
                    _connection.close();
                    return stock;
                }
            }
            _connection.close();
        } catch ( SQLException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        } catch ( ClassNotFoundException ex ) {
            Logging.log(Logging.ERROR, ex, this.getClass().getName());
        }
        return -1;
    }

}

